package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.bean.MymienBean;
import com.bean.MymienBean;
import com.util.DBSource;



public class MymienDao {
    DBSource db=new DBSource();
    Connection conn=null;
    PreparedStatement ps;
    

	
	//����ҵķ����Ϣ
	public int AddMymien(MymienBean bean){
		int rows=0;
		System.out.println("22222");
		try {
			conn=db.getConn();
			String insert="insert into mymien(titleinfo,textinfo,addtime,state,userid,picturePath,type,comment1,comment2,comment3) values(?,?,?,?,?,?,?,0,?,?)";
			ps=conn.prepareStatement(insert);//ִ��sql
			ps.setString(1, bean.getTitleInfo());
			ps.setString(2, bean.getTextInfo());
			ps.setString(3,bean.getAddTime());
			ps.setInt(4,1);
			ps.setInt(5, bean.getUserId());
			ps.setString(6, bean.getPicturePath());
			ps.setInt(7, bean.getType());
			ps.setString(8, bean.getComment2());
			ps.setString(9, bean.getComment3());
			
			rows=ps.executeUpdate();//������Ӱ�������
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		
		return rows;
	}
	//�޸��ҵķ����Ϣ
	public int UpdateMymien(MymienBean bean){
		int rows=0;
		
		try {
			conn=db.getConn();
			String update="update mymien set titleinfo=?,textinfo=?,picturePath=?,comment2=?,comment3=?,state=?,addtime=? where id=?";
			ps=conn.prepareStatement(update);
			ps.setString(1, bean.getTitleInfo());
			ps.setString(2, bean.getTextInfo());
			
			ps.setString(3, bean.getPicturePath());
		
			ps.setString(4, bean.getComment2());
			ps.setString(5, bean.getComment3());
			ps.setInt(6, bean.getState());
			ps.setString(7, bean.getAddTime());
			ps.setInt(8, bean.getId());
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		
		return rows;
	}
	public int UpdateOne(MymienBean bean){
		int rows=0;
		
		try {
			conn=db.getConn();
			String update="update mymien set comment1=? where id=?";
			ps=conn.prepareStatement(update);
			ps.setString(1, bean.getComment1());
			ps.setInt(2, bean.getId());
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return rows;
	}
	public int UpdateState(MymienBean bean){
		int rows=0;
		
		try {
			conn=db.getConn();
			String update="update mymien set state=? where id=?";
			ps=conn.prepareStatement(update);
			ps.setInt(1, bean.getState());
			ps.setInt(2, bean.getId());
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return rows;
	}
	public int DeleteMymien(int id){
		int rows=0;
		
		try {
			conn=db.getConn();
			String delete="delete from mymien where id=?";
			ps=conn.prepareStatement(delete);
			ps.setInt(1, id);
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		
		return rows;
	}
	//��ѯȫ��
	public List<MymienBean> SelectAll(int type ,int userId){
		List<MymienBean> mymiens=new ArrayList<MymienBean>();
		
		try {
			conn=db.getConn();
			String select="select * from mymien where type=? and userId=? and state=1";
			
			ps=conn.prepareStatement(select);
			ps.setInt(1,type);
			ps.setInt(2, userId);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				MymienBean mymien=new MymienBean();
				mymien.setId(rs.getInt("id"));
				mymien.setAddTime(rs.getString("addtime"));
				mymien.setComment1(rs.getString("comment1"));
				mymien.setComment2(rs.getString("comment2"));
				mymien.setComment3(rs.getString("comment3"));
				mymien.setPicturePath(rs.getString("picturepath"));
				mymien.setState(rs.getInt("state"));
				mymien.setTextInfo(rs.getString("textinfo"));
				mymien.setTitleInfo(rs.getString("titleinfo"));
				mymien.setType(rs.getInt("type"));
				mymien.setUserId(rs.getInt("userid"));
				mymiens.add(mymien);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mymiens;
	}
	
	//��ѯȫ��
		public List<MymienBean> SelectAll(int userId){
			List<MymienBean> mymiens=new ArrayList<MymienBean>();
			
			try {
				conn=db.getConn();
				String select="select * from mymien where userId=? and state=1";
				
				ps=conn.prepareStatement(select);
				
				ps.setInt(1, userId);
				ResultSet rs=ps.executeQuery();
				while(rs.next()){
					MymienBean mymien=new MymienBean();
					mymien.setId(rs.getInt("id"));
					mymien.setAddTime(rs.getString("addtime"));
					mymien.setComment1(rs.getString("comment1"));
					mymien.setComment2(rs.getString("comment2"));
					mymien.setComment3(rs.getString("comment3"));
					mymien.setPicturePath(rs.getString("picturepath"));
					mymien.setState(rs.getInt("state"));
					mymien.setTextInfo(rs.getString("textinfo"));
					mymien.setTitleInfo(rs.getString("titleinfo"));
					mymien.setType(rs.getInt("type"));
					mymien.setUserId(rs.getInt("userid"));
					mymiens.add(mymien);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return mymiens;
		}
	//���id��
	public MymienBean SelectById(int id){
		MymienBean mymien=null;
		try {
			conn=db.getConn();
			String select="select * from mymien where id=?";
			ps=conn.prepareStatement(select);
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				mymien=new MymienBean();
				mymien.setId(rs.getInt("id"));
				mymien.setAddTime(rs.getString("addtime"));
			    mymien.setComment1(rs.getString("comment1"));
			    mymien.setComment2(rs.getString("comment2"));
			    mymien.setComment3(rs.getString("comment3"));
			    mymien.setPicturePath(rs.getString("picturepath"));
			    mymien.setState(rs.getInt("state"));
			    mymien.setTextInfo(rs.getString("textinfo"));
			    mymien.setTitleInfo(rs.getString("titleinfo"));
			    mymien.setType(rs.getInt("type"));
			    mymien.setUserId(rs.getInt("userid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mymien;
	}
	public MymienBean SelZhiding(int type,int userId){
		MymienBean mymien=null;
		try {
			conn=db.getConn();
			String select="select * from mymien where comment1=(select max(comment1) from mymien) and state=1 and type=? and userid=?";
			ps=conn.prepareStatement(select);
			ps.setInt(1, type);
			ps.setInt(2, userId);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				mymien=new MymienBean();
				mymien.setId(rs.getInt("id"));
				mymien.setAddTime(rs.getString("addtime"));
			    mymien.setComment1(rs.getString("comment1"));
			    mymien.setComment2(rs.getString("comment2"));
			    mymien.setComment3(rs.getString("comment3"));
			    mymien.setPicturePath(rs.getString("picturepath"));
			    mymien.setState(rs.getInt("state"));
			    mymien.setTextInfo(rs.getString("textinfo"));
			    mymien.setTitleInfo(rs.getString("titleinfo"));
			    mymien.setType(rs.getInt("type"));
			    mymien.setUserId(rs.getInt("userid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return mymien;
	}
	@Test
	public void test1(){
		MymienBean ss=SelZhiding(1,1);
		
			System.out.println(ss.getTitleInfo());
		
	}
	public List<MymienBean> SelNotZ(int type,int userId){
		List<MymienBean> list=new ArrayList<MymienBean>();
		try {
			conn=db.getConn();
			String select="select * from mymien where comment1<(select max(comment1) from mymien) and state=1 and type=? and userid=?";
			ps=conn.prepareStatement(select);
			ps.setInt(1, type);
			ps.setInt(2, userId);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				MymienBean mymien=new MymienBean();
				mymien.setId(rs.getInt("id"));
				mymien.setAddTime(rs.getString("addtime"));
			    mymien.setComment1(rs.getString("comment1"));
			    mymien.setComment2(rs.getString("comment2"));
			    mymien.setComment3(rs.getString("comment3"));
			    mymien.setPicturePath(rs.getString("picturepath"));
			    mymien.setState(rs.getInt("state"));
			    mymien.setTextInfo(rs.getString("textinfo"));
			    mymien.setTitleInfo(rs.getString("titleinfo"));
			    mymien.setType(rs.getInt("type"));
			    mymien.setUserId(rs.getInt("userid"));
			    list.add(mymien);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	@Test
	public void test(){
		List<MymienBean> list=selectPages(1,5,1,1);
		for(MymienBean bean:list){
			System.out.println(bean.getTextInfo());
		}
		
	}
	public List<MymienBean> selectZDPages(int currentRows,int rowsPerPage,int userid,int type){
		List<MymienBean> list=new ArrayList<MymienBean>();
		Connection conn=null;
		try{
			conn=db.getConn();
			String sql="select * from Mymien where comment1=(select max(comment1) from mymien) and userid=? and type=?  limit ?,?  ";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, type);
			ps.setInt(3,currentRows);
			ps.setInt(4,rowsPerPage);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				MymienBean mymien=new MymienBean();
				mymien.setId(rs.getInt("id"));
				mymien.setAddTime(rs.getString("addtime"));
				mymien.setComment1(rs.getString("comment1"));
				mymien.setComment2(rs.getString("comment2"));
				mymien.setComment3(rs.getString("comment3"));
				mymien.setPicturePath(rs.getString("picturepath"));
				mymien.setState(rs.getInt("state"));
				mymien.setTextInfo(rs.getString("textinfo"));
				mymien.setTitleInfo(rs.getString("titleinfo"));
				mymien.setType(rs.getInt("type"));
				mymien.setUserId(rs.getInt("userid"));
				list.add(mymien);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return list;
	}
	public List<MymienBean> selectPages(int currentRows,int rowsPerPage,int userid,int type){
		List<MymienBean> list=new ArrayList<MymienBean>();
		Connection conn=null;
		try{
			conn=db.getConn();
			String sql="select * from Mymien where comment1<(select max(comment1) from mymien) and userid=? and type=? order by comment1 desc limit ?,?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, type);
			ps.setInt(3,currentRows);
			ps.setInt(4,rowsPerPage);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				MymienBean mymien=new MymienBean();
				mymien.setId(rs.getInt("id"));
				mymien.setAddTime(rs.getString("addtime"));
				mymien.setComment1(rs.getString("comment1"));
				mymien.setComment2(rs.getString("comment2"));
				mymien.setComment3(rs.getString("comment3"));
				mymien.setPicturePath(rs.getString("picturepath"));
				mymien.setState(rs.getInt("state"));
				mymien.setTextInfo(rs.getString("textinfo"));
				mymien.setTitleInfo(rs.getString("titleinfo"));
				mymien.setType(rs.getInt("type"));
				mymien.setUserId(rs.getInt("userid"));
				list.add(mymien);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return list;
	}
	
}

